package com.css.fxfzxczh.modules.lossAssessment.houseCategoryArea.repository;

import com.css.fxfzxczh.base.dict.repository.entity.DictDirectoryEntity;
import com.css.fxfzxczh.constants.YNEnum;
import com.css.fxfzxczh.modules.lossAssessment.houseCategoryArea.entity.HouseCategoryArea;
import com.css.fxfzxczh.modules.lossAssessment.houseCategoryArea.param.QueryParam;
import com.css.fxfzxczh.modules.lossAssessment.houseCategoryArea.vo.BuildGridFindAllVo;
import com.css.fxfzxczh.modules.lossAssessment.houseCategoryArea.vo.HouseCategoryAreaVo;
import com.css.fxfzxczh.modules.synthesizeInfoManage.earEveManage.entity.EarEveManage;
import com.css.fxfzxczh.modules.synthesizeInfoManage.earEveManage.service.EarEveManageService;
import com.css.fxfzxczh.util.PlatformObjectUtils;
import com.css.fxfzxczh.util.PlatformSessionUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author Dbj
 * @date 2023年05月31日  17:53
 */
@Repository
public class HouseCategoryAreaRepository {
    @Resource
    JdbcTemplate jdbcTemplate;
    @Resource
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @Resource
    EarEveManageService earEveManageService;

    public int deletePcData(String type) {
        String sql = "delete from xczh_pc_data ";
        if(StringUtils.isNotBlank(type)){
            sql += " where type='"+type+"'";
        }
        return jdbcTemplate.update(sql);
    }

    public void batchSavePcData(List<BuildGridFindAllVo> vos) {
        SqlParameterSource[] beanSources = SqlParameterSourceUtils.createBatch(vos.toArray());
        String sql =
                "insert into xczh_pc_data(xpd_id," +
                        "type," +
                        "total_area," +
                        "residence," +
                        "office," +
                        "business," +
                        "medic," +
                        "other," +
                        "province," +
                        "city," +
                        "county," +
                        "town," +
                        "province_code," +
                        "city_code," +
                        "county_code," +
                        "town_code," +
                        "create_time," +
                        "create_user,new_update,geom) values("
                        + ":xpdId, :type, :totalArea,:residence,:office,:business,:medic, :other,:province,"
                        + ":city,:county,:town,:provinceCode,:cityCode,:countyCode,:townCode,:createTime,"
                        + ":createUser,:newUpdate,ST_GeometryFromText(:geom,4490))";
        namedParameterJdbcTemplate.batchUpdate(sql, beanSources);
    }

    public List<HouseCategoryArea> getHouseCategoryArea(QueryParam queryParam) {
        StringBuilder sql = new StringBuilder("select * from xczh_survey_house_category_area where del_flag='").append(YNEnum.N).append("'");
        if(PlatformObjectUtils.isNotEmpty(queryParam.getProvince())){
            //省
            sql.append(" and province='").append(queryParam.getProvince()).append("'");
        }
        if(PlatformObjectUtils.isNotEmpty(queryParam.getCity())){
            //市
            sql.append(" and city='").append(queryParam.getCity()).append("'");
        }
        if(PlatformObjectUtils.isNotEmpty(queryParam.getArea())){
            //区县
            sql.append(" and county='").append(queryParam.getArea()).append("'");
        }
        if(PlatformObjectUtils.isNotEmpty(queryParam.getHouseUseType())){
            //房屋使用用途
            sql.append(" and house_purpose_code='").append(queryParam.getHouseUseType()).append("'");
        }
//        if(PlatformObjectUtils.isNotEmpty(queryParam.getXsemCode())){
//            //房屋使用用途
//            sql.append(" and xsem_code='").append(queryParam.getXsemCode()).append("'");
//        }
        EarEveManage currentEvent = earEveManageService.getCurrentEvent();
        sql.append(" and xsem_id='").append(currentEvent.getXsemId()).append("' order  by evaluation_subarea_name,province_code,city_code,county_code");
        return jdbcTemplate.query(sql.toString(),new BeanPropertyRowMapper<>(HouseCategoryArea.class));
    }

    public List<BuildGridFindAllVo> getPcData(List<String> codeList) {
        String sql = "select * from xczh_pc_data where county_code in (:codeList)";
        Map idsMap = new HashMap();
        idsMap.put("codeList", codeList);
        return namedParameterJdbcTemplate.query(sql, idsMap, new BeanPropertyRowMapper<>(BuildGridFindAllVo.class));
    }

    public List<BuildGridFindAllVo> getPcData(String geom) {
        String sql = "select * from xczh_pc_data where 1=1 and ST_Intersects (geom, '"+geom+"' )";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(BuildGridFindAllVo.class));
    }

    public Boolean getIntersectData(String geom, String geom1) {
        String sql = "SELECT ST_Intersects(?,?)";
        return jdbcTemplate.queryForObject(sql, Boolean.class, geom, geom1);
    }
    public Boolean getIntersectData1(String geom, String geom1) {
        String sql = "SELECT ST_Intersects(st_geometryfromtext(?,4490),?)";
        return jdbcTemplate.queryForObject(sql, Boolean.class, geom, geom1);
    }

    public String getSTIntersection(String geomText, String geomText1) {
        String sql = "SELECT ST_Intersection(?,ST_GeomFromText(?,4490)) as geom where ST_Intersects(?,ST_GeomFromText(?,4490))";

        List<String> strings = jdbcTemplate.queryForList(sql, String.class, geomText, geomText1, geomText, geomText1);
        return PlatformObjectUtils.isNotEmpty(strings)?strings.get(0):"";
    }

    public void batchSave(List<HouseCategoryArea> areaList) {
        SqlParameterSource[] beanSources = SqlParameterSourceUtils.createBatch(areaList.toArray());
        String sql =
                "insert into xczh_survey_house_category_area(" +
                        "xshca_id," +
                        "xstm_id," +
                        "xsem_id," +
                        "evaluation_subarea_name," +
                        "province," +
                        "city," +
                        "county," +
                        "province_code," +
                        "city_code," +
                        "county_code," +
                        "house_purpose_code," +
                        "house_purpose_name," +
                        "v1_area," +
                        "v2_area," +
                        "v3_area," +
                        "v4_area," +
                        "v5_area," +
                        "v6_area," +
                        "v7_area," +
                        "v8_area," +
                        "v9_area," +
                        "v10_area," +
                        "create_time," +
                        "create_user_id," +
                        "del_flag) values("
                        + ":xshcaId, :xstmId, :xsemId,:evaluationSubareaName,:province,:city,:county, :provinceCode,:cityCode,"
                        + ":countyCode,:housePurposeCode,:housePurposeName,:v1Area,:v2Area,:v3Area,:v4Area,:v5Area,"
                        + ":v6Area,:v7Area,:v8Area,:v9Area,:v10Area,:createTime,:createUserId,:delFlag)";
        namedParameterJdbcTemplate.batchUpdate(sql, beanSources);
    }

    public void deleteByEarthQuake(String xsemId) {
        //String sql = "update xczh_survey_house_category_area set del_flag='1' ,update_user_id='"+ PlatformSessionUtils.getUserId() +"',update_time=now() where xsem_id='"+xsemId+"' and del_flag='0'";
        String sql = "delete from xczh_survey_house_category_area where xsem_id='"+xsemId+"'";
        jdbcTemplate.update(sql);
    }

    public int deleteById(String xshcaId) {
        StringBuilder sql = new StringBuilder("update xczh_survey_house_category_area set del_flag='1' ,update_user_id='"+ PlatformSessionUtils.getUserId() +"',update_time=now() where xshca_id = :xshcaId and del_flag='0'");
        Map idMap = new HashMap();
        idMap.put("xshcaId", xshcaId);
        return namedParameterJdbcTemplate.update(sql.toString(), idMap);
    }

    public HouseCategoryArea findById(String xshcaId) {
        String sql = "select * from xczh_survey_house_category_area where del_flag='0' and xshca_id='"+xshcaId+"'";
        List<HouseCategoryArea> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(HouseCategoryArea.class));
        return PlatformObjectUtils.isEmpty(query)?new HouseCategoryArea():query.get(0);
    }

    public int toUpdate(HouseCategoryArea area) {
        StringBuilder builder = new StringBuilder("update xczh_survey_house_category_area set update_user_id='").append(PlatformSessionUtils.getUserId()).append("',update_time=now()");
        if(PlatformObjectUtils.isNotEmpty(area.getV1Area())){
            builder.append(",v1_area=").append(area.getV1Area());
        }
        if(PlatformObjectUtils.isNotEmpty(area.getV2Area())){
            builder.append(",v2_area=").append(area.getV2Area());
        }
        if(PlatformObjectUtils.isNotEmpty(area.getV3Area())){
            builder.append(",v3_area=").append(area.getV3Area());
        }
        if(PlatformObjectUtils.isNotEmpty(area.getV4Area())){
            builder.append(",v4_area=").append(area.getV4Area());
        }
        if(PlatformObjectUtils.isNotEmpty(area.getV5Area())){
            builder.append(",v5_area=").append(area.getV5Area());
        }
        if(PlatformObjectUtils.isNotEmpty(area.getV6Area())){
            builder.append(",v6_area=").append(area.getV6Area());
        }
        if(PlatformObjectUtils.isNotEmpty(area.getV7Area())){
            builder.append(",v7_area=").append(area.getV7Area());
        }
        if(PlatformObjectUtils.isNotEmpty(area.getV8Area())){
            builder.append(",v8_area=").append(area.getV8Area());
        }
        if(PlatformObjectUtils.isNotEmpty(area.getV9Area())){
            builder.append(",v9_area=").append(area.getV9Area());
        }
        if(PlatformObjectUtils.isNotEmpty(area.getV10Area())){
            builder.append(",v10_area=").append(area.getV10Area());
        }
        builder.append(" where xshca_id='").append(area.getXshcaId()).append("'");
        return jdbcTemplate.update(builder.toString());
    }


    public List<HouseCategoryArea> getSumArea(String xsemId, String xstmId) {
        String sql = "select province,city,county,house_purpose_code,sum(v1_area) v1Area,sum(v2_area) v2Area,sum(v3_area) v3Area,sum(v4_area) v4Area,sum(v5_area) v5Area," +
                "sum(v6_area) v6Area,sum(v7_area) v7Area,sum(v8_area) v8Area,sum(v9_area) v9Area,sum(v10_area) v10Area " +
                "from xczh_survey_house_category_area " +
                "where xsem_id=? and xstm_id=?  and del_flag ='0' " +
                "group by province,city,county,house_purpose_code";
        try {
            List<HouseCategoryArea> houseCategoryAreaList = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(HouseCategoryArea.class), xsemId, xstmId);
            return houseCategoryAreaList;
        }catch (EmptyResultDataAccessException e){
            return null;
        }

    }

    public List<String> getxstmIdByXsemId(String xsemId) {
        String sql = "select distinct xstm_id from xczh_survey_house_category_area where xsem_id= ? ";
        List<String> list = jdbcTemplate.queryForList(sql, String.class,xsemId);
        return list;
    }

    public HouseCategoryArea getHouseCategoryAreaByXstmId(String xstmId,String xsemId) {
        String sql = "select sum(v1_area) v1Area,sum(v2_area) v2Area,sum(v3_area) v3Area,sum(v4_area) v4Area,sum(v5_area) v5Area," +
                "sum(v6_area) v6Area,sum(v7_area) v7Area,sum(v8_area) v8Area,sum(v9_area) v9Area,sum(v10_area) v10Area " +
                "from xczh_survey_house_category_area  where xstm_id='"+xstmId+"' and del_flag='0' and xsem_id='"+xsemId+"' ";
        try {
            HouseCategoryArea buildingEntity = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(HouseCategoryArea.class));
            return buildingEntity;
        }catch (EmptyResultDataAccessException e){
            return null;
        }
    }

    public HouseCategoryArea getTypeTotalAreaByXstmId(String xstmId) {
        String sql = "SELECT  xstm_id, sum(v1_area) v1_area,sum(v2_area) v2_area,sum(v3_area) v3_area,sum(v4_area) v4_area,sum(v5_area) v5_area," +
                "sum(v6_area) v6_area,sum(v7_area) v7_area,sum(v8_area) v8_area,sum(v9_area) v9_area,sum(v10_area) v10_area " +
                "FROM xczh_survey_house_category_area where xstm_id='"+xstmId+"' and del_flag='0' GROUP BY xstm_id;";
        try {
            HouseCategoryArea houseCategoryArea = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(HouseCategoryArea.class));
            return houseCategoryArea;
        }catch (EmptyResultDataAccessException e){
            return null;
        }
    }

    public List<HouseCategoryArea> getALlCountyByXsemCode(String xsemId) {
        String sql = "SELECT county FROM xczh_survey_house_category_area where del_flag='0' and xsem_id='"+xsemId+"' group by county";
        List<HouseCategoryArea> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(HouseCategoryArea.class));
        return list;
    }

    public HouseCategoryArea getTypeTotalAreaByXsemCode(String xsemId) {
        String sql = "SELECT COALESCE(SUM(v1_area), 0) as v1_area," +
                "COALESCE(SUM(v2_area), 0) as v2_area," +
                "COALESCE(SUM(v3_area), 0) as v3_area," +
                "COALESCE(SUM(v4_area), 0) as v4_area," +
                "COALESCE(SUM(v5_area), 0) as v5_area," +
                "COALESCE(SUM(v6_area), 0) as v6_area," +
                "COALESCE(SUM(v7_area), 0) as v7_area," +
                "COALESCE(SUM(v8_area), 0) as v8_area," +
                "COALESCE(SUM(v9_area), 0) as v9_area," +
                "COALESCE(SUM(v10_area), 0) as v10_area " +
                "FROM xczh_survey_house_category_area " +
                "WHERE xsem_id = '"+xsemId+"'";
        try {
            HouseCategoryArea houseCategoryArea = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(HouseCategoryArea.class));
            return houseCategoryArea;
        }catch (EmptyResultDataAccessException e){
            return null;
        }
    }

    public List<HouseCategoryAreaVo> getALlSubzoneCountyByXsemCode(String xsemId) {
        String sql = "SELECT county,evaluation_subarea_name evaluationSubareaName FROM xczh_survey_house_category_area " +
                "where del_flag='0' and xsem_id='"+xsemId+"' group by county,evaluation_subarea_name";
        List<HouseCategoryAreaVo> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(HouseCategoryAreaVo.class));
        return list;
    }

    public List<HouseCategoryArea> getTotalAreaListByXsemCode(String xsemId) {
        String sql = "SELECT * FROM xczh_survey_house_category_area " +
                "where del_flag='0' and xsem_id='"+xsemId+"' order by evaluation_subarea_name,province,city,county ";
        List<HouseCategoryArea> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(HouseCategoryArea.class));
        return list;
    }
}
